package br.com.iqt.tests;

import br.com.iqt.AgentRewriter;
import br.com.iqt.ConnectionDbms;
import br.com.iqt.Dbms;
import br.com.iqt.exception.DbmsException;
import br.com.iqt.exception.HeuristicsSelectedException;
import br.com.iqt.exception.SqlInputException;
import br.com.iqt.midlleware.Midlleware;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

public class Testador {
    
private ArrayList workLoad;
private Dbms dbms;
private Midlleware midlleware;
private Connection connection;
private int executionType;

private final static int NORMAL = 1;
private final static int ADVISOR = 2;
private final static int MIDLLEWARE = 3;
private final static int HEURISTICS_CASH_MIDLLEWARE = 4;
private final static int SQL_CASH_MIDLLEWARE = 5;

    public Testador(ArrayList workLoad, Dbms dbms, int executionType) throws FileNotFoundException, IOException, ClassNotFoundException, SQLException {
        this.workLoad = workLoad;
        this.dbms = dbms;
        
        switch(executionType){
            case Testador.NORMAL: 
                this.executionType = Testador.NORMAL;
                this.connection = ConnectionDbms.getConnection(dbms);break;
            case Testador.ADVISOR: 
                this.executionType = Testador.ADVISOR;
                this.connection = ConnectionDbms.getConnection(dbms);break;
            case Testador.MIDLLEWARE: 
                this.executionType = Testador.MIDLLEWARE;
                midlleware = new Midlleware(dbms, AgentRewriter.noCash);break;
            case Testador.HEURISTICS_CASH_MIDLLEWARE: 
                this.executionType = Testador.HEURISTICS_CASH_MIDLLEWARE;
                midlleware = new Midlleware(dbms, AgentRewriter.heuristicCash);break;
            case Testador.SQL_CASH_MIDLLEWARE: 
                this.executionType = Testador.SQL_CASH_MIDLLEWARE;
                midlleware = new Midlleware(dbms, AgentRewriter.sqlCash);break;
        }
        
    }

    public ArrayList getWorkLoad() {
        return workLoad;
    }

    public void setWorkLoad(ArrayList workLoad) {
        this.workLoad = workLoad;
    }

    public Dbms getDbms() {
        return dbms;
    }

    public void setDbms(Dbms dbms) {
        this.dbms = dbms;
    }

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }
    
    private ArrayList gerateRandomList(int size){
        ArrayList aux = new ArrayList();
        for (int i = 0; i < size; i++) {
            aux.add(i);
        }
        
        ArrayList randomList = new ArrayList();
        while(!aux.isEmpty()){
            int randomIndex = (int) (Math.random()*aux.size());
            int number = (Integer) aux.get(randomIndex);
            aux.remove(randomIndex);
            randomList.add(number);
        }
        
        return randomList;
    }
    
    public void close() throws SQLException{
        if(this.executionType == Testador.ADVISOR || this.executionType == Testador.NORMAL) {
            this.connection.close();
        }
        else {
            this.midlleware.close();
        }
    }
    
    public long sequencialTest(int times) throws SqlInputException, ParseException, br.com.iqt.zql.ParseException, ClassNotFoundException, SQLException, FileNotFoundException, IOException, DbmsException, HeuristicsSelectedException{
        ArrayList workLoad = new ArrayList();

        //reescreve as consultas antes de serem executadas(advisor)
        if(this.executionType == Testador.ADVISOR){
            AgentRewriter agentRewriter = new AgentRewriter(this.dbms);
            for (int i = 0; i < this.workLoad.size(); i++) {
                workLoad.add(agentRewriter.analyseStatement(this.workLoad.get(i).toString()));
            }
        }else {//fornece as consultas a serem reescritas em tempo de execução(midlleware)
            workLoad.addAll(this.workLoad);
        }
            
        int size = workLoad.size();
       
        //executa as consultas
        long beginTime = System.nanoTime(); 
        while (times > 0) {
            for (int i = 0; i < size; i++) {
                String sql = (String) workLoad.get(i);
                if(this.executionType == Testador.ADVISOR 
                        || this.executionType == Testador.NORMAL) {
                    ConnectionDbms.executeQuery(this.connection, sql);
                }
                else {
                    midlleware.executeQuery(sql);
                }
            }
            times--;
        }
         
        
        long endTime = System.nanoTime();
        
        return (endTime - beginTime);
    }
    
    public long fixedRandomTest(int times) throws SqlInputException, ParseException, br.com.iqt.zql.ParseException, ClassNotFoundException, SQLException, FileNotFoundException, IOException, DbmsException, HeuristicsSelectedException{
        ArrayList workLoad = new ArrayList();

        //reescreve as consultas antes de serem executadas(advisor)
        if(this.executionType == Testador.ADVISOR){
            AgentRewriter agentRewriter = new AgentRewriter(this.dbms);
            for (int i = 0; i < this.workLoad.size(); i++) {
                workLoad.add(agentRewriter.analyseStatement(this.workLoad.get(i).toString()));
            }
        }else {//fornece as consultas a serem reescritas em tempo de execução(midlleware)
            workLoad.addAll(this.workLoad);
        }
            
        int size = workLoad.size();
        
        ArrayList indexes = this.gerateRandomList(size);
        
        //executa as consultas
        long beginTime = System.nanoTime();
        while (times > 0) {
            for (int i = 0; i < size; i++) {
                int index = (Integer)indexes.get(i);
                String sql = (String) workLoad.get(index);
                if(this.executionType == Testador.ADVISOR 
                        || this.executionType == Testador.NORMAL) {
                    ConnectionDbms.executeQuery(this.connection, sql);
                }
                else {
                    midlleware.executeQuery(sql);
                }
            }
            times--;
        }
        
        long endTime = System.nanoTime();
        
        return (endTime - beginTime);
    }
    
    public long randomTest(int times) throws SqlInputException, ParseException, br.com.iqt.zql.ParseException, ClassNotFoundException, SQLException, FileNotFoundException, IOException, DbmsException, HeuristicsSelectedException{
        ArrayList workLoad = new ArrayList();

        //reescreve as consultas antes de serem executadas(advisor)
        if(this.executionType == Testador.ADVISOR){
            AgentRewriter agentRewriter = new AgentRewriter(this.dbms);
            for (int i = 0; i < this.workLoad.size(); i++) {
                workLoad.add(agentRewriter.analyseStatement(this.workLoad.get(i).toString()));
            }
        }else {//fornece as consultas a serem reescritas em tempo de execução(midlleware)
            workLoad.addAll(this.workLoad);
        }
            
        int size = workLoad.size();
        
        //executa as consultas
        long beginTime = System.nanoTime();
        while (times > 0) {
            ArrayList indexes = this.gerateRandomList(size);
            for (int i = 0; i < size; i++) {
                int index = (Integer)indexes.get(i);
                String sql = (String) workLoad.get(index);
                if(this.executionType == Testador.ADVISOR 
                        || this.executionType == Testador.NORMAL) {
                    ConnectionDbms.executeQuery(this.connection, sql);
                }
                else {
                    midlleware.executeQuery(sql);
                }
            }
            times--;
        }
        
        long endTime = System.nanoTime();
        
        return (endTime - beginTime);
    }


    public static void main(String[] args) {
        long time;
        //Dbms dbms = new Dbms(Dbms.POSTGRESQL, "localhost", "5432", "tpch", "postgres", "123456");
        Dbms dbms = new Dbms(Dbms.ORACLE, "localhost", "5432", "tpch", "sa", "123456");
        
        ArrayList workload = new ArrayList();
        
        //CARGA DE TRABALHO TPCH SINTETICA
        workload.add("SELECT * FROM part where p_retailprice < ANY( SELECT p_retailprice FROM part where p_container='JUMBO CAN')");
       /* workload.add("SELECT distinct(o_orderkey), o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment FROM orders");
        workload.add("SELECT distinct(c_custkey), c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment FROM customer;");
        workload.add("SELECT distinct(c_custkey), c_name, c_address, c_phone, c_acctbal, c_mktsegment, c_comment, n_name, r_name FROM customer, nation, region where c_nationkey=n_nationkey and n_regionkey=r_regionkey");
        workload.add("SELECT distinct(o_orderkey), o_totalprice, c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment FROM customer, orders where c_custkey=o_custkey");
        workload.add("SELECT distinct(o_orderkey), o_totalprice, c_custkey, c_name FROM customer, orders where c_custkey= o_custkey;");
        workload.add("SELECT max(l_extendedprice), min(l_extendedprice) FROM lineitem group by l_extendedprice");
        workload.add("SELECT o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate FROM orders group by o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate");
        workload.add("SELECT max(o_totalprice), min(o_totalprice) FROM customer, orders where c_custkey=o_custkey group by c_name,o_totalprice having o_totalprice <10000");
        workload.add("SELECT o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate FROM orders group by o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate having o_totalprice <10000");
        workload.add("SELECT * FROM part where p_mfgr='Manufacturer#5' or p_size=10 or p_retailprice=910");
        workload.add("SELECT l_orderkey FROM lineitem where l_quantity<20 or l_linenumber>5");
        workload.add("select *  FROM lineitem where l_quantity*2 = 50");
        workload.add("select *  FROM lineitem where l_quantity*2 = (SELECT max(l_quantity) FROM lineitem)");
        workload.add("select *  FROM lineitem where l_quantity + (SELECT min(l_quantity) FROM lineitem) + 10 = (SELECT max(l_quantity) FROM lineitem)");
        workload.add("SELECT o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate FROM orders where o_totalprice < some ( select l_orderkey FROM lineitem where l_quantity + (SELECT min(l_quantity) FROM lineitem) + 10 = (SELECT max(l_quantity) FROM lineitem) )");
        workload.add("SELECT o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate FROM orders where o_totalprice < any ( select l_orderkey FROM lineitem where l_quantity + (SELECT min(l_quantity) FROM lineitem) + 10 = (SELECT max(l_quantity) FROM lineitem) )");
        workload.add("SELECT o_orderkey FROM orders where o_totalprice > all ( select l_orderkey FROM lineitem where l_quantity + (SELECT min(l_quantity) FROM lineitem) - 10 = (SELECT max(l_quantity) FROM lineitem) )");
        workload.add("SELECT o_totalprice FROM orders where o_totalprice > ALL (SELECT o_totalprice FROM orders group by o_totalprice having o_totalprice > 1000000)");
        workload.add("SELECT o_totalprice FROM orders where o_totalprice < some (SELECT o_totalprice FROM orders group by o_totalprice having o_totalprice > 400000)");
        workload.add("SELECT o_totalprice FROM orders where o_totalprice < any (SELECT o_totalprice FROM orders group by o_totalprice having o_totalprice > 400000)");
        workload.add("SELECT distinct(o_orderkey) FROM orders where o_totalprice < any (SELECT o_totalprice FROM orders group by o_totalprice having o_totalprice > 400000)");
        workload.add("SELECT distinct(o_orderkey) FROM orders where o_totalprice > all ( select distinct(l_orderkey) FROM lineitem where l_quantity + (SELECT min(l_quantity) FROM lineitem) - 10 = (SELECT max(l_quantity) FROM lineitem) )");
        workload.add("SELECT distinct(o_orderkey) FROM orders where o_totalprice < SOME ( SELECT o_totalprice FROM orders where o_orderpriority = '2-HIGH')");
        workload.add("SELECT distinct(p_partkey) FROM part where p_retailprice > ALL( SELECT p_retailprice FROM part where p_container='JUMBO CAN')");*/
        
        Testador test = null;
        try {
            FileWriter fileWriter;
            File file = new File("output.txt");
            fileWriter = new FileWriter(file, false);
            PrintWriter printWriter = new PrintWriter(fileWriter);
            
            //executa a carga de trabalho no formato normal
            //test = new Testador(workload, dbms, Testador.NORMAL);
            
            //executa a carga de trabalho após ter sido reescrita
            test = new Testador(workload, dbms, Testador.ADVISOR);
            
            //executa a carga de trabalho utilizando o midlleware com todas as heuristicas
            //test = new Testador(workload, dbms, Testador.MIDLLEWARE);
            
            //executa a carga de trabalho utilizando o midlleware com a cash de heuristicas
            //test = new Testador(workload, dbms, Testador.HEURISTICS_CASH_MIDLLEWARE);
            
            //executa a carga de trabalho utilizando o midlleware com a cash de sql
            //test = new Testador(workload, dbms, Testador.SQL_CASH_MIDLLEWARE);
            
            System.out.println("Teste de 1 interação");
            printWriter.println("Teste de 1 interação");
            time = test.sequencialTest(1);
            System.out.println("Tempo = " + time);
            printWriter.println("Tempo = " + time);
            printWriter.flush();
            
            System.out.println("Teste de 2 interações");
            printWriter.println("Teste de 2 interações");
            time = test.sequencialTest(2);
            System.out.println("Tempo = " + time);
            printWriter.println("Tempo = " + time);
            printWriter.flush();
            
            System.out.println("Teste de 4 interações");
            printWriter.println("Teste de 4 interações");
            time = test.sequencialTest(4);
            System.out.println("Tempo = " + time);
            printWriter.println("Tempo = " + time);
            printWriter.flush();
            
            System.out.println("Teste de 8 interações");
            printWriter.println("Teste de 8 interações");
            time = test.sequencialTest(8);
            System.out.println("Tempo = " + time);
            printWriter.println("Tempo = " + time);
            printWriter.flush();
            
            System.out.println("Teste de 16 interações");
            printWriter.println("Teste de 16 interações");
            time = test.sequencialTest(16);
            System.out.println("Tempo = " + time);
            printWriter.println("Tempo = " + time);
            printWriter.flush();
            
            System.out.println("Teste de 32 interações");
            printWriter.println("Teste de 32 interações");
            time = test.sequencialTest(32);
            System.out.println("Tempo = " + time);
            printWriter.println("Tempo = " + time);
            printWriter.flush();
            
            
            printWriter.close();
            test.close();
            
        } catch (FileNotFoundException ex) {
            Logger.getLogger(Testador.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(Testador.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Testador.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(Testador.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SqlInputException ex) {
            Logger.getLogger(Testador.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseException ex) {
            Logger.getLogger(Testador.class.getName()).log(Level.SEVERE, null, ex);
        } catch (br.com.iqt.zql.ParseException ex) {
            Logger.getLogger(Testador.class.getName()).log(Level.SEVERE, null, ex);
        } catch (DbmsException ex) {
            Logger.getLogger(Testador.class.getName()).log(Level.SEVERE, null, ex);
        } catch (HeuristicsSelectedException ex) {
            Logger.getLogger(Testador.class.getName()).log(Level.SEVERE, null, ex);
        }



        
        
    }
}
